using System;
using System.Collections.Generic;
using System.Text;
using ECommerce.DAL;
using System.Data.SqlClient;
using System.Data;

namespace Entity
{
    [System.ComponentModel.DataObject]
    public class TblUserExtended:TblUser
    {

        public static CustomCollection<TblUser> GetUserByUserName(string UserName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@UserName", SqlDbType.NVarChar,500, ParameterDirection.Input, UserName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_USER_GET_TBLUSER_BY_USERNAME);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblUserCollectionFromReader);
            CustomCollection<TblUser> objCollection = ((CustomCollection<TblUser>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetCustomerUserCount(string searchCustomerName)
        {
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();

            string searchName = (string.IsNullOrEmpty(searchCustomerName) ? "%" : searchCustomerName);

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@SearchName", SqlDbType.NVarChar, 500, ParameterDirection.Input, searchName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_USER_GET_CUSTOMER_USER_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }
        public static CustomCollection<TblUser> GetNonDeletedCustomerUsersCOnditionPaged(string Condition, string orderBy, int firstItemPosition, int maxItem)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();

            AddParameterToSqlCmd(sqlCmd, "@Condition", SqlDbType.NVarChar, 2000, ParameterDirection.Input, Condition);
            AddParameterToSqlCmd(sqlCmd, "@Orderby", SqlDbType.NVarChar, 500, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, firstItemPosition);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, maxItem);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, "sp_TblUser_Get_Customer_User_Condition_Paged");
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblUserCollectionFromReader);
            CustomCollection<TblUser> objCollection = ((CustomCollection<TblUser>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }
        public static int GetNonDeletedCustomerUserConditionCount(string searchCustomerName)
        {
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();

            string searchName = (string.IsNullOrEmpty(searchCustomerName) ? "%" : searchCustomerName);

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@Condition", SqlDbType.NVarChar, 2000, ParameterDirection.Input, searchName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "sp_TblUser_Get_Customer_User_Condition_Paged_Count");
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }
        public static int GetNonDeletedCustomerUserCount(string searchCustomerName)
        {
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();

            string searchName = (string.IsNullOrEmpty(searchCustomerName) ? "%" : searchCustomerName);

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@SearchName", SqlDbType.NVarChar, 500, ParameterDirection.Input, searchName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_USER_GET_NON_DELETED_CUSTOMER_USER_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<TblUser> GetNonDeletedCustomerUsersPaged(string searchCustomerName, string orderBy, int firstItemPosition, int maxItem)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();

            string searchName = (string.IsNullOrEmpty(searchCustomerName) ? "%" : searchCustomerName);

            AddParameterToSqlCmd(sqlCmd, "@SearchName", SqlDbType.NVarChar, 500, ParameterDirection.Input, searchName);
            AddParameterToSqlCmd(sqlCmd, "@Orderby", SqlDbType.NVarChar, 500, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, firstItemPosition);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, maxItem);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_USER_GET_NON_DELETED_CUSTOMER_USER_PAGED);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblUserCollectionFromReader);
            CustomCollection<TblUser> objCollection = ((CustomCollection<TblUser>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static CustomCollection<TblUser> GetCustomerUsersPaged(string searchCustomerName, string orderBy, int firstItemPosition, int maxItem)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();

            string searchName = (string.IsNullOrEmpty(searchCustomerName) ? "%" : searchCustomerName);

            AddParameterToSqlCmd(sqlCmd, "@SearchName", SqlDbType.NVarChar, 500, ParameterDirection.Input, searchName);
            AddParameterToSqlCmd(sqlCmd, "@Orderby", SqlDbType.NVarChar, 500, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, firstItemPosition);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, maxItem);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_USER_GET_CUSTOMER_USER_PAGED);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblUserCollectionFromReader);
            CustomCollection<TblUser> objCollection = ((CustomCollection<TblUser>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static CustomCollection<TblUser> GetCustomerUsersPaged2(string orderBy, int firstItemPosition, int maxItem)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();
            AddParameterToSqlCmd(sqlCmd, "@Orderby", SqlDbType.NVarChar, 500, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, firstItemPosition);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, maxItem);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, "sp_TblUser_Get_NonDeleted_Customer_User_Paged2");
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblUserCollectionFromReader);
            CustomCollection<TblUser> objCollection = ((CustomCollection<TblUser>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }


        public static CustomCollection<TblUser> GetAllNonDeletedCustomerUsers(string searchCustomerName, string sortBy)
        {
            return GetNonDeletedCustomerUsersPaged(searchCustomerName,sortBy,0,99999);
        }

        public static CustomCollection<TblUser> GetAllCustomerUsers(string searchCustomerName)
        {
            return GetCustomerUsersPaged(searchCustomerName, "", 0, 99999);
        }

        public static CustomCollection<TblUser> GetUserByCondition(string condition)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@Condition", SqlDbType.NVarChar, 2000, ParameterDirection.Input, condition);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_USER_GET_TBLUSER_BY_CONDITION);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblUserCollectionFromReader);
            CustomCollection<TblUser> objCollection = ((CustomCollection<TblUser>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        private const string SP_TBL_USER_GET_TBLUSER_BY_USERNAME = "sp_TblUser_GetTblUserByUserName";
        private const string SP_TBL_USER_GET_NON_DELETED_CUSTOMER_USER_COUNT = "sp_TblUser_Get_NonDeleted_Customer_User_Count";
        private const string SP_TBL_USER_GET_CUSTOMER_USER_COUNT = "sp_TblUser_Get_Customer_User_Count";
        private const string SP_TBL_USER_GET_NON_DELETED_CUSTOMER_USER_PAGED = "sp_TblUser_Get_NonDeleted_Customer_User_Paged";
        private const string SP_TBL_USER_GET_CUSTOMER_USER_PAGED = "sp_TblUser_Get_Customer_User_Paged";
        private const string SP_TBL_USER_GET_TBLUSER_BY_CONDITION = "sp_TblUser_GetTblUserByCondition";
    }
}

